// createCoreLogicSLR.do
// This file creates corelogic_slr.dta that is needed to run main.do
// Inputs: intermediate, election_EC, countyincome, countypopulation, buyer_countyeducation, county_2005_2016, unemp_2001_2016, tests_2009_2016, crime_2001_2016, build_2001_2016, flood_2001_2016, prop_FEMA_zone, Yale_Buyer_2014_timing, Gallup_gw_vars_2010_2016, conforming_limit_all
// Outputs: corelogic_slr.dta
// Date last updated: 1/27/2025

// This .do file combines many of the above input files into the dataset used for the final specification, corelogic_slr.dta.

********************************************************************************	
* Load data and merge in controls
********************************************************************************

// Load cleaned propriety data (not included in replication package)
use "$pathf\intermediate", clear

// Merge controls
	merge m:1 buyer_fips electionyear using "$pathi\election_EC"
	drop if _merge == 2
	drop _merge
	gen year = sale_year
	merge m:1 buyer_fips year using "$pathi\countyincome"
	drop if _merge == 2
	drop _merge
	merge m:1 buyer_fips year using "$pathi\countypopulation"
	drop if _merge == 2
	drop _merge

// Merge additional controls for Appendix robustness checks
	* ACS 5-year education variables
	merge m:1 buyer_fips year using "$pathi\buyer_countyeducation"
	drop if _merge == 2
	drop total place hs_or_less some_col bachelors post_grad 
	ren share_hs_or_less share_hs_or_less5
	ren share_some_col share_some_col5
	ren share_bachelors share_bachelors5
	ren share_post_grad share_post_grad5
	label variable share_hs_or_less5 "hs or less share based on 5-year ACS estimates"
	label variable share_some_col5 "some col share based on 5-year ACS estimates"
	label variable share_bachelors5  "bachelors share based on 5-year ACS estimates"
	label variable share_post_grad5 "post grad share based on 5-year ACS estimates"
	rename _merge _mergeACSedu

// SEER county information (race, age, etc.) and 1-year education information 
	merge m:1 buyer_fips year using "$pathi\county_2005_2016"
	drop if _merge == 2
	ren share_hs_or_less share_hs_or_less1
	ren share_some_col share_some_col1
	ren share_bachelors share_bachelors1
	ren share_post_grad share_post_grad1
	label variable share_hs_or_less1 "hs or less share based on 1-year ACS estimates"
	label variable share_some_col1 "some col share based on 1-year ACS estimates"
	label variable share_bachelors1  "bachelors share based on 1-year ACS estimates"
	label variable share_post_grad1 "post grad share based on 1-year ACS estimates"
	ren _merge _mergeACS

// Building, crime, test scores, and unemployment data 
	merge m:1 buyer_fips year using "$pathi\unemp_2001_2016.dta"
	drop if _merge==2
	ren _merge unemp_merge 
	merge m:1 buyer_fips year using "$pathi\tests_2009_2016.dta"
	drop if _merge==2
	ren _merge tests_merge 
	merge m:1 fips_code year using "$pathi\crime_2001_2016.dta"
	drop if _merge==2
	ren _merge crime_merge 
	merge m:1 fips_code year using "$pathi\build_2001_2016.dta"
	drop if _merge==2
	ren _merge build_merge 
	merge m:1 fips_code year using "$pathi\flood_2001_2016.dta"
	drop if _merge==2 
	ren _merge prev_flood_merge
	
// Create county share minority variable 
	gen share_minority=pop_share_other + pop_share_Black + pop_share_Asian + pop_share_Hispanic
	* Create county share children variable 
	gen share_child=1-(pop_share_age_18_29 + pop_share_age_30_49 + pop_share_age_50_64 + pop_share_age_65plus)
	* Make flood variable zero if no recorded floods
	replace prev_floods=0 if prev_floods==.

// FEMA flood zones
	egen id=group(state lon lat)
	merge m:1 id using "$pathi\prop_FEMA_zone.dta"

	destring buyer_zip_dw, replace force
	destring buyer_income, replace force
	destring buyer_population, replace force

// Create additional survey-based belief variables
	* Above median worry and timing from Yale survey
	sum worried_buyer, d 
	gen above_worried_buyer = 1 if worried_buyer >= r(p50)
	replace above_worried_buyer = 0 if above_worried_buyer == . 
	replace above_worried_buyer = . if worried_buyer == .
	merge m:1 buyer_fips using "$pathi\Yale_Buyer_2014_timing"
	drop if _merge==2 
	drop _merge 
	ren timing timing_buyer 
	sum timing_buyer, d
	gen above_timing_buyer = 1 if timing_buyer >= r(p50)
	replace above_timing_buyer = 0 if above_timing_buyer == . 
	replace above_timing_buyer = . if timing_buyer == .
	
// Merge in county level beliefs data based on Gallup survey
	merge m:1 buyer_fips year using "$pathi\Gallup_gw_vars_2010_2016.dta"
	drop if _merge ==2
	drop _merge 
	
********************************************************************************	
* Clean data more
********************************************************************************

// Create additional SLR categories
	gen SLR = 0 //No risk
	replace SLR = 1 if underwater_6ft== 1 //Moderate risk, 4,5,6ft
	replace SLR = 2 if underwater_3ft == 1 //High risk, 1,2,3ft"

	* continuous
	gen cSLR = 0
	replace cSLR = 1 if underwater_6ft== 1
	replace cSLR = 2 if underwater_5ft == 1
	replace cSLR = 3 if underwater_4ft == 1
	replace cSLR = 4 if underwater_3ft == 1
	replace cSLR = 5 if underwater_2ft  == 1
	replace cSLR = 6 if underwater_1ft  == 1 

	* only 3ft and above
	gen SLR3 = 0
	replace SLR3 = 1 if underwater_6ft== 1 // 6
	replace SLR3 = 2 if underwater_5ft == 1 // 5
	replace SLR3 = 3 if underwater_4ft == 1 // 4
	replace SLR3 = 4 if underwater_3ft == 1 // 1,2,3

	ren underwater_6ft under
	label variable leverage_0 "leveraged"

// Flag loans with nonstandard (not 15 nor 30 year) terms
	gen flag = 1
	replace flag = 0 if mtg_term == .
	replace flag = 0 if mtg_term == 15
	replace flag = 0 if mtg_term == 30

// Create conforming loan dummies (used in Tables 5 and A15)
	*Specify high-cost counties for 2001-2008
	gen high_cost_county = 1 if fips_code == 09001 | fips_code == 12021 | fips_code == 12087 | fips_code == 13133 | fips_code == 25005 | fips_code == 25007 | fips_code == 25009 | fips_code == 25017 | fips_code == 25019 | fips_code == 25021 | fips_code == 25023 | fips_code == 25025 | fips_code == 24003 | fips_code == 24005 | fips_code == 24009 | fips_code == 24013 | fips_code == 24017 | fips_code == 24021 | fips_code == 24025 | fips_code == 24027 | fips_code == 24031 | fips_code == 24033 | fips_code == 24035 | fips_code == 37029 | fips_code == 37053 | fips_code == 37073 | fips_code == 37095 | fips_code == 37139 | fips_code == 37143 | fips_code == 33015 | fips_code == 33017 | fips_code == 34003 | fips_code == 34013 | fips_code == 34017 | fips_code == 34019 | fips_code == 34023 | fips_code == 34025 | fips_code == 34027 | fips_code == 34029 | fips_code == 34031 | fips_code == 34035 | fips_code == 34037 | fips_code == 34039 | fips_code == 36005 | fips_code == 36027 | fips_code == 36047 | fips_code == 36059 | fips_code == 36061 | fips_code == 36071 | fips_code == 36079 | fips_code == 36081 | fips_code == 36085 | fips_code == 36087 | fips_code == 36103 | fips_code == 36119 | fips_code == 42103 | fips_code == 44001 | fips_code == 44003 | fips_code == 44005 | fips_code == 44007 | fips_code == 44009 | fips_code == 51003 | fips_code == 51510 | fips_code == 51007 | fips_code == 51013 | fips_code == 51029 | fips_code == 51033 | fips_code == 51036 | fips_code == 51550 | fips_code == 51041 | fips_code == 51043 | fips_code == 51570 | fips_code == 51047 | fips_code == 51049 | fips_code == 51053 | fips_code == 51059 | fips_code == 51600 | fips_code == 51610 | fips_code == 51061 | fips_code == 51065 | fips_code == 51630 | fips_code == 51073 | fips_code == 51075 | fips_code == 51079 | fips_code == 51650 | fips_code == 51085 | fips_code == 51087 | fips_code == 51670 | fips_code == 51093 | fips_code == 51095 | fips_code == 51097 | fips_code == 51101 | fips_code == 51103 | fips_code == 51107 | fips_code == 51109 | fips_code == 51683 | fips_code == 51685 | fips_code == 51115 | fips_code == 51125 | fips_code == 51127 | fips_code == 51700 | fips_code == 51710 | fips_code == 51730 | fips_code == 51735 | fips_code == 51740 | fips_code == 51145 | fips_code == 51149 | fips_code == 51153 | fips_code == 51157 | fips_code == 51760 | fips_code == 51177 | fips_code == 51179 | fips_code == 51181 | fips_code == 51183 | fips_code == 51810 | fips_code == 51187 | fips_code == 51830 | fips_code == 51199
	
// Merge conforming loan limits for 2009-2016
	merge m:1 fips_code sale_year using "$pathi\conforming_limit_all"
	drop if _merge == 2
	drop _merge
	
// Create one-unit limits for years 2001-2008 
	replace OneUnitLimit = 417000 if sale_year == 2006 | sale_year == 2007 | sale_year == 2008
	replace OneUnitLimit = 801950 if (sale_year == 2006 & high_cost_county == 1) | (sale_year == 2007 & high_cost_county == 1) | (sale_year == 2008 & high_cost_county == 1)
	replace OneUnitLimit = 359650 if sale_year == 2005
	replace OneUnitLimit = 691600 if sale_year == 2005 & high_cost_county == 1
	replace OneUnitLimit = 333700 if sale_year == 2004
	replace OneUnitLimit = 641650 if sale_year == 2004 & high_cost_county == 1
	replace OneUnitLimit = 322700 if sale_year == 2003
	replace OneUnitLimit = 620500 if sale_year == 2003 & high_cost_county == 1
	replace OneUnitLimit = 300700 if sale_year == 2002
	replace OneUnitLimit = 578150 if sale_year == 2002 & high_cost_county == 1
	replace OneUnitLimit = 275000 if sale_year == 2001
	replace OneUnitLimit = 528700 if sale_year == 2001 & high_cost_county == 1
	
// Dummies for conforming loan limits 
	gen conforming = 1 if mtg_amt1 < OneUnitLimit
	replace conforming = 0 if mtg_amt1 > OneUnitLimit & mtg_amt1 != .
	replace conforming = . if mtg_amt1 == .
	gen price_above_lim = 1 if sale_price1 > OneUnitLimit
	replace price_above_lim = 0 if price_above_lim == .

// Save
	save "$pathf\corelogic_slr.dta", replace